CREATE TABLE [dbo].[HistoryGroups]
(
	Id int IDENTITY(1,1) NOT NULL, 
	Name varchar(200) NOT NULL
)

CREATE TABLE [dbo].[HistoryGroupMembers]
(
	Id int IDENTITY(1,1) NOT NULL, 
	HistoryGroupId INT NOT NULL,
	HistoryId INT NOT NULL,
	TargetRate [decimal] (18,8) NOT NULL, --Tasa que uso para reconvertir moneda local del RR Historico
	TargetSpacesConversion [decimal] (18,8) NOT NULL, --Tasa que uso para reconvertir a metros los datos de espacios
	TargetFuelsConversion [decimal] (18,8) NOT NULL --Tasa que uso para reconvertir a m3 Fuels
)

ALTER TABLE [dbo].[HistoryGroups]
	ADD CONSTRAINT [PK_HistoryGroups]
	PRIMARY KEY (Id)
	
	ALTER TABLE [dbo].[HistoryGroupMembers]
	ADD CONSTRAINT [PK_HistoryGroupMembers]
	PRIMARY KEY (Id)
	
		ALTER TABLE [dbo].[HistoryGroupMembers]
	ADD CONSTRAINT FK_HistoryGroups_HistoryGroupMembers_HistoryGroupId
	FOREIGN KEY (HistoryGroupId)
	REFERENCES HistoryGroups (Id)
	
		ALTER TABLE [dbo].[HistoryGroupMembers]
	ADD CONSTRAINT FK_History_HistoryGroupMembers_HistoryId
	FOREIGN KEY (HistoryId)
	REFERENCES History (Id)
	
ALTER TABLE RangeReviewBenchmarks
ADD HistoryGroupId INT NULL

ALTER TABLE [dbo].[RangeReviewBenchmarks]
	ADD CONSTRAINT [FK_HistoryGroups_RangeReviewBenchmarks_HistoryGroupId] 
	FOREIGN KEY (HistoryGroupId)
	REFERENCES HistoryGroups (Id)
	
	
	
	USE [ARR]
GO
/****** Object:  StoredProcedure [dbo].[History_InsertFromTempHistoryDetails]    Script Date: 11/10/2011 19:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[History_InsertFromTempHistoryDetails]
AS

BEGIN TRAN

--Primero veo si ya existe History para el mismo country y el mismo pais. En ese caso borro los details y el history y vuelvo a importar
declare @existingIdsTable TABLE (Id INT)  

INSERT INTO @existingIdsTable (Id)
Select distinct h.Id From History h
Inner Join Countries c on h.CountryId = c.Id
Inner Join TempHistoryDetails t on t.Country = c.Name and t.Range = h.Title

DELETE FROM HistoryDetails Where HistoryId in (Select Id From @existingIdsTable)
IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRAN
		RAISERROR('Error borrando detalle del historico.', 16, 1)
		RETURN
	END
	
DELETE FROM History Where Id in (Select Id From @existingIdsTable)
IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRAN
		RAISERROR('Error borrando el historico.', 16, 1)
		RETURN
	END

--Vuelvo a Insertar History
INSERT INTO History (CountryId, Title, RangeReviewId, Rate, SpacesConversion, FuelsConversion)
Select distinct c.Id CountryId, t.Range Title, rr.Id RangeReviewId, t.Rate, t.SpacesConversion, t.FuelsConversion From TempHistoryDetails t
Inner Join Countries c on t.Country = c.Name
Left Outer Join RangeReviews rr on rr.CountryId = c.Id and rr.Title = t.Range

IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRAN
		RAISERROR('Error insertando el historico.', 16, 1)
		RETURN
	END

--Inserto HistoryDetails
INSERT INTO HistoryDetails (HistoryId, CategoryId, Category, Universe, Role, AverageVolume, AverageSales, AverageGIM, AverageGIMP, AverageDelistedVolume, AverageDelistedSales, AverageDelistedGIM, SS_VolumeCY, SS_SalesCY, SS_GIMCY, SS_PIDiffCY, SS_ChargeOffCY, SS_RebatesCY, SS_InventoryCY, SS_VolumePY, SS_SalesPY, SS_GIMPY, SS_PIDiffPY, SS_ChargeOffPY, SS_RebatesPY, SS_InventoryPY, Gondola, TransactionArea, GrabAndGo, WIC, OpenCooler, Freezer, Forecourt, OtherExhibition, SKUsWithSales, NewSKUs, SKUsInSalesFiles, DelistProposal, Sales10, Sales20, Sales30, Sales40, Sales50, Sales60, Sales70, Sales80, Sales90, Sales100, Bottom5, HurdleRate, ActiveCategories, CategoriesWithInvAnalysis, Country, Range)
Select h.Id HistoryId, CategoryId, Category, Universe, Role, AverageVolume, AverageSales, AverageGIM, AverageGIMP, AverageDelistedVolume, AverageDelistedSales, AverageDelistedGIM, SS_VolumeCY, SS_SalesCY, SS_GIMCY, SS_PIDiffCY, SS_ChargeOffCY, SS_RebatesCY, SS_InventoryCY, SS_VolumePY, SS_SalesPY, SS_GIMPY, SS_PIDiffPY, SS_ChargeOffPY, SS_RebatesPY, SS_InventoryPY, Gondola, TransactionArea, GrabAndGo, WIC, OpenCooler, Freezer, Forecourt, OtherExhibition, SKUsWithSales, NewSKUs, SKUsInSalesFiles, DelistProposal, Sales10, Sales20, Sales30, Sales40, Sales50, Sales60, Sales70, Sales80, Sales90, Sales100, Bottom5, HurdleRate, ActiveCategories, CategoriesWithInvAnalysis, Country, Range
From TempHistoryDetails t
Inner Join Countries c on t.Country = c.Name
Inner Join History h on h.CountryId = c.Id and h.Title = t.Range

IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRAN
		RAISERROR('Error insertando detalles del historico.', 16, 1)
		RETURN
	END


COMMIT TRAN
